﻿//-----------------------------------------------------------------------
// <copyright file="SQLDbTransaction.cs" >
//     All rights reserved.
//     THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY 
//     KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
//     IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
//     PARTICULAR PURPOSE.
// </copyright>
// <author>Marais van Zyl</author>
// <email>marais@projexis.co.za</email>
// <CreatedDate>2010/01/26</CreatedDate>
//-----------------------------------------------------------------------
namespace Accountability4net.MSSQLDbProvider
{
    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Linq;

    /// <summary>
    /// Defines a transaction that debits and credits two accounts
    /// </summary>
    public class SQLDbTransaction : BaseTransaction
    {
        /// <summary>
        /// the datetime the transaction took place
        /// </summary>
        private DateTimeOffset timestamp_;
        
        /// <summary>
        /// the description of the transaction
        /// </summary>
        private string description_;
        
        /// <summary>
        /// indicates whether the transaction is committed
        /// </summary>
        private bool isCommited_;
        
        /// <summary>
        /// the id of the transaction
        /// </summary>
        private Guid id_;

        /// <summary>
        /// the provider being used
        /// </summary>
        private SQLDbProvider provider_;

        /// <summary>
        /// Initializes a new instance of the SQLDbTransaction class
        /// </summary>
        /// <param name="provider">the provider being used by the transaction</param>
        public SQLDbTransaction(SQLDbProvider provider)
            : this()
        {
            this.provider_ = provider;
        }

        /// <summary>
        /// Initializes a new instance of the SQLDbTransaction class
        /// </summary>
        protected SQLDbTransaction()
            : base()
        {
        }

        /// <summary>
        /// Gets or sets the datetime the transaction was created
        /// </summary>
        public override DateTimeOffset Timestamp
        {
            get
            {
                return this.timestamp_;
            }

            set
            {
                this.timestamp_ = value;
            }
        }

        /// <summary>
        /// Gets or sets the description of the transaction
        /// </summary>
        public override string Description
        {
            get
            {
                return this.description_;
            }

            set
            {
                this.description_ = value;
                OnPropertyChanged("Description");
            }
        }

        /// <summary>
        /// Gets a value indicating whether the transaction was committed
        /// </summary>
        public override bool IsCommitted
        {
            get
            {
                return this.isCommited_;
            }
        }

        /// <summary>
        /// Gets or sets the ID of the transaction
        /// </summary>
        public override Guid ID
        {
            get
            {
                if (this.id_ == null || this.id_ == Guid.Empty)
                {
                    this.id_ = Guid.NewGuid();
                }

                return this.id_;
            }

            set
            {
                this.id_ = value;
            }
        }

        public static SQLDbTransaction Create(SQLDbProvider provider, DateTimeOffset timeStamp)
        {
            SQLDbTransaction transaction = new SQLDbTransaction(provider);
            transaction.ID = Guid.NewGuid();
            transaction.Timestamp = timeStamp;
            transaction.WirePropertyChanged();
            string sql = @"INSERT INTO [AccountTransaction] 
                ([ID], [Commited], [CreatedDate], [CreatedMilliseconds])
                VALUES
                (@id, @commited, @createdDate, @createdMilliseconds)";
            using (var command = provider.CreateCommand(sql))
            {
                command.Parameters.AddWithValue("@id", transaction.ID);
                command.Parameters.AddWithValue("@commited", false);
                command.Parameters.AddWithValue("@createdDate", transaction.Timestamp.DateTime);
                command.Parameters.AddWithValue("@createdMilliseconds", TimeSpan.FromTicks(transaction.Timestamp.Ticks).TotalMilliseconds);
                int rows = command.ExecuteNonQuery();
            }

            return transaction;
        }

        /// <summary>
        /// Creates a transaction
        /// </summary>
        /// <param name="provider">the provider being used to create the transaction</param>
        /// <returns>the newly created transaction</returns>
        public static SQLDbTransaction Create(SQLDbProvider provider)
        {
            return Create(provider, DateTimeOffset.Now);
        }

        /// <summary>
        /// Retrieves all the transactions that has been committed
        /// </summary>
        /// <param name="provider">the provider being used to retrieve the transactions</param>
        /// <returns>the list of transactions found</returns>
        public static IList<ITransaction> Retrieve(SQLDbProvider provider)
        {
            var result = new List<ITransaction>();
            string sql = "SELECT * FROM [AccountTransaction] WITH (nolock) WHERE [Commited] = 1";
            using (var command = provider.CreateCommand(sql))
            {
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var transaction = new SQLDbTransaction(provider);
                        transaction.ID = reader.GetGuid(0);
                        transaction.WirePropertyChanged();
                        result.Add(transaction);
                    }

                    reader.Close();
                }
            }

            return result;
        }

        /// <summary>
        /// Retrieves all the transactions that has been committed for a specific account
        /// </summary>
        /// <param name="provider">the provider being used to retrieve the transactions</param>
        /// <param name="account">the account of which's transactions to retrieve</param>
        /// <returns>the list of transactions found</returns>
        public static IList<ITransaction> Retrieve(SQLDbProvider provider, IAccount account)
        {
            var result = new List<ITransaction>();
            string sql = "SELECT t.* FROM [AccountTransaction] t "
                + " INNER JOIN [AccountAction] "
                + " ON [AccountAction].[transaction_id] = t.Id "
                + " INNER JOIN [Account] "
                + " ON [AccountAction].[account_id] = [account].[Number] "
                + " WHERE t.[Commited] = 1"
                + " AND  [Account].[Number] = @account";
            using (var command = provider.CreateCommand(sql))
            {
                var accountParameter = new SqlParameter("@account", account.Number);
                command.Parameters.Add(accountParameter);
                CreateFromCommand(provider, command)
                    .ToList()
                    .ForEach(t => 
                    {
                        SQLDbAction.Retrieve(provider, account, t)
                            .ToList()
                            .ForEach(a => t.Add(a));
                        result.Add(t);
                    });
            }

            return result;
        }

        /// <summary>
        /// Retrieves all the transactions that has been committed for a specific account
        /// </summary>
        /// <param name="provider">the provider being used to retrieve the transactions</param>
        /// <param name="account">the account of which's transactions to retrieve</param>
        /// <param name="after">the datetime afterwhich transactions should be retrieved for</param>
        /// <returns>the list of transactions found</returns>
        public static IList<ITransaction> Retrieve(SQLDbProvider provider, IAccount account, DateTimeOffset? after)
        {
            var result = new List<ITransaction>();
            string sql = "SELECT t.* FROM [AccountTransaction] t, [account], [AccountAction]"
                + " WHERE [Commited] = 1"
                + " AND [AccountAction].[transaction_id] = t.Id"
                + " AND [AccountAction].[account_id] = [account].[Number]"
                + " AND [Account].[Number] = @account";
            using (var command = provider.CreateCommand(sql))
            {
                command.Parameters.AddWithValue("@account", account.Number);
                AddAfterCondition(command, after);
                CreateFromCommand(provider, command)
                    .ToList()
                    .ForEach(t =>
                    {
                        SQLDbAction.Retrieve(provider, account, t)
                            .ToList()
                            .ForEach(a => t.Add(a));
                        result.Add(t);
                    });
            }

            return result;
        }

        /// <summary>
        /// Retrieves all the transactions that has been committed for a specific account
        /// </summary>
        /// <param name="provider">the provider being used to retrieve the transactions</param>
        /// <param name="account">the account of which's transactions to retrieve</param>
        /// <param name="after">the datetime afterwhich transactions should be retrieved for</param>
        /// <param name="before">the datetime beforewhich transactions should be retrieved for</param>
        /// <returns>the list of transactions found</returns>
        public static IList<ITransaction> Retrieve(SQLDbProvider provider, IAccount account, DateTimeOffset? after, DateTimeOffset? before)
        {
            var result = new List<ITransaction>();
            string sql = "SELECT t.* FROM [AccountTransaction] t, [account], [AccountAction]"
                + " WHERE [Commited] = 1"
                + " AND [AccountAction].[transaction_id] = t.Id"
                + " AND [AccountAction].[account_id] = [account].[Number]"
                + " AND [account].[Number] = @account";
            using (var command = provider.CreateCommand(sql))
            {
                command.Parameters.AddWithValue("@account", account.Number);
                AddAfterCondition(command, after);
                AddBeforeCondition(command, before);
                 CreateFromCommand(provider, command)
                    .ToList()
                    .ForEach(t => 
                    {
                        SQLDbAction.Retrieve(provider, account, t)
                            .ToList()
                            .ForEach(a => t.Add(a));
                        result.Add(t);
                    });
            }

            return result;
        }
        
        /// <summary>
        /// Impliments the PropertyChanged event of the class
        /// </summary>
        public void WirePropertyChanged()
        {
            PropertyChanged += this.SQLDbTransaction_PropertyChanged;
        }

        /// <summary>
        /// Commits the transaction
        /// </summary>
        public override void Commit()
        {
            using (var transaction = this.provider_.Connection.BeginTransaction())
            {
                this.provider_.Transaction = transaction;
                try
                {
                    base.Commit();
                    foreach (IAction action in this)
                    {
                        if (action.Type == ActionType.CREDIT)
                        {
                            action.Account.Credit(action.Amount, this);
                        }
                        else if (action.Type == ActionType.DEBIT)
                        {
                            action.Account.Debit(action.Amount, this);
                        }
                    }

                    var sql = "UPDATE [AccountTransaction] SET [Commited] = 1 WHERE [Id] = @id";
                    using (var command = this.provider_.CreateCommand(sql))
                    {
                        command.Parameters.AddWithValue("@id", this.ID.ToString());
                        int rows = command.ExecuteNonQuery();
                    }

                    transaction.Commit();
                    this.isCommited_ = true;
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
                finally
                {
                    this.provider_.Transaction = null;
                }
            }
        }

        /// <summary>
        /// Updates sets the transaction id on the action when the action is added to the transaction
        /// </summary>
        /// <param name="action">the action that will be added to the transaction</param>
        public override void Add(IAction action)
        {
            if (!(action is SQLDbAction))
            {
                throw new ArgumentException("Parameter must be of type SQLAdoDbAction", "action");
            }

            string sql = "UPDATE [AccountAction] SET [Transaction_id] = @transaction_id WHERE [Id] = @action_id";
            using (var command = this.provider_.CreateCommand(sql))
            {
                command.Parameters.AddWithValue("@transaction_id", this.ID.ToString());
                command.Parameters.AddWithValue("@action_id", ((SQLDbAction)action).Id);
                int rows = command.ExecuteNonQuery();
            }

            base.Add(action);
        }

        /// <summary>
        /// Adds a where condition to only select certain records before a certain point in time
        /// </summary>
        /// <param name="command">the command used to do the select</param>
        /// <param name="before">the datetime value to be used in the select statement</param>
        private static void AddBeforeCondition(SqlCommand command, DateTimeOffset? before)
        {
            if (before.HasValue)
            {
                var beforeMilliseconds = TimeSpan.FromTicks(before.Value.Ticks).TotalMilliseconds;
                command.CommandText += " AND t.[CreatedMilliseconds] <= @before";
                command.Parameters.AddWithValue("@before", beforeMilliseconds);
            }
        }

        /// <summary>
        /// Adds a where condition to only select certain records after a certain point in time
        /// </summary>
        /// <param name="command">the command used to do the select</param>
        /// <param name="after">the datetime value to be used in the select statement</param>
        private static void AddAfterCondition(SqlCommand command, DateTimeOffset? after)
        {
            if (after.HasValue)
            {
                var afterMilliseconds = TimeSpan.FromTicks(after.Value.Ticks).TotalMilliseconds;
                command.CommandText += " AND t.[CreatedMilliseconds] > @after";
                command.Parameters.AddWithValue("@after", afterMilliseconds);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider">the provider being used</param>
        /// <param name="command"></param>
        /// <returns></returns>
        private static IEnumerable<SQLDbTransaction> CreateFromCommand(SQLDbProvider provider, SqlCommand command)
        {
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    var transaction = new SQLDbTransaction(provider);
                    transaction.ID = new Guid(reader["Id"].ToString());
                    
                    // SqlDataReader.GetString would give NullReferenceException if Description is null
                    transaction.Description = reader.GetValue(reader.GetOrdinal("Description")) as string;
                    transaction.WirePropertyChanged();
                    yield return transaction;
                }
            }
        }

        /// <summary>
        /// Updates changes being made to the transaction.
        /// </summary>
        /// <param name="sender">the sender of the event</param>
        /// <param name="e">the <see cref="System.ComponentModel.PropertyChangedEventArgs"/> arguments</param>
        private void SQLDbTransaction_PropertyChanged(object sender, System.ComponentModel.PropertyChangedEventArgs e)
        {
            string sql = "UPDATE [AccountTransaction] SET [Description] = @description WHERE [Id] = @id";
            using (var command = this.provider_.CreateCommand(sql))
            {
                command.Parameters.AddWithValue("@id", this.ID);
                command.Parameters.AddWithValue("@description", this.Description);
                int rows = command.ExecuteNonQuery();
            }
        }
    }
}
